﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Net;
using System.Windows;
using System.IO;
using System.Data.SQLite;
using System.Windows.Forms;
using System.Security.Cryptography;
using System.Drawing;
using MySql.Data.MySqlClient;

namespace EvePos
{
    class SupportFn
    {
        public string filepath, connstr, connstruser, connstrstat, assetscacheduntil = "no current data", dirli = "ï", dirre = "ð", timestampcorpassets = "";
        public string mysqlconnstr = "", apiserver = "https://api.eveonline.com/", comname;
        public int apiErrors;
        public bool new_api_data, debugging = false, updatereallocation = false, testdata = false;
        public string[] chs={"CORP 1","CORP 2","CORP 3","CORP 4","CORP 5","CORP 6","CORP 7"};

        public bool fetchServerValues(long corpid)
        {
            MySqlConnection con = new MySqlConnection(mysqlconnstr);
            MySqlCommand com = new MySqlCommand("select itemID,groupName,hasSov,cleared,clearedBy,clearedTimestamp from tower where corpID=" + corpid, con);
            writeDebug("reader: "+com.CommandText);
            MySqlDataReader dr;
            string sqlstr;
            bool update = false;
            try
            {
                con.Open();
                // fuel loop
                dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {
                        sqlstr = readSql("select itemID,groupName,hasSov,cleared,clearedBy,clearedTimestamp,stateTimestamp from apiStarbases where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                        if (sqlstr != "")
                        {
                            if(dr.GetValue(1).ToString()!=getSubStr(1,sqlstr))
                            {   // groupName
                                writeSql("update apiStarbases set groupName='" + dr.GetValue(1) + "' where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                update = true;
                            }
                            if (dr.GetValue(2).ToString()=="1" && getSubStr(2, sqlstr)=="False")
                            {   // sovereignty
                                writeSql("update apiStarbases set hasSov=1 where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                update = true;
                            }
                            if (dr.GetValue(2).ToString() == "0" && getSubStr(2, sqlstr) == "True")
                            {   // sovereignty
                                writeSql("update apiStarbases set hasSov=0 where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                update = true;
                            }
                            if (dr.GetValue(3).ToString() == "0" && getSubStr(3, sqlstr) == "True")
                            {   // refuled
                                writeSql("update apiStarbases set cleared=0,clearedBy='',clearedTimestamp=null where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                update = true;
                            }
                            if (dr.GetValue(3).ToString() == "1" && getSubStr(3, sqlstr) == "False")
                            {   // refuled
                                writeSql("update apiStarbases set cleared=1,clearedBy='" + dr.GetValue(4) + "',clearedTimestamp='" + dr.GetDateTime(5).ToString("yyyy-MM-dd HH:mm:ss") + "' where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                update = true;
                            }
                            // clear if entry too old
                            if (!dr.IsDBNull(5) && getSubStr(6, sqlstr) != "")
                            {
                                DateTime dt1 = dr.GetDateTime(5);
                                DateTime dt2 = Convert.ToDateTime(getSubStr(6, sqlstr)); // in fact stateTimestamp + 1h (cachedTime)
                                if (dt2.Subtract(dt1).TotalHours > 1.0)
                                {
                                    writeSql("update apiStarbases set cleared=0,clearedBy='',clearedTimestamp=null where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                    writeMySql("update tower set cleared=0,clearedBy='',clearedTimestamp=null where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                    update = true;
                                }
                            }
                            if (dr.GetValue(3).ToString() == "1" && getSubStr(3, sqlstr) == "True")
                            {
                                string dt1 = dr.GetDateTime(5).ToString("dd.MM.yyyy HH:mm:ss");
                                string dt2 = getSubStr(5, sqlstr);
                                if (dr.GetValue(4).ToString() != getSubStr(4, sqlstr) || (dt1 != dt2))
                                {
                                    writeSql("update apiStarbases set cleared=1,clearedBy='" + dr.GetValue(4) + "',clearedTimestamp='" + dr.GetDateTime(5).ToString("yyyy-MM-dd HH:mm:ss") + "' where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                    update = true;
                                }
                            }
                        }
                    } while (dr.Read());
                }
                dr.Close();
                // silo loop
                com = new MySqlCommand("select itemID,direction,remark,cleared,clearedBy,clearedTimestamp from silo where corpID=" + corpid, con);
                writeDebug(com.CommandText);
                dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {
                        sqlstr = readSql("select itemID,direction,remark,cleared,clearedBy,clearedTimestamp from reactions where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                        if (sqlstr != "")
                        {
                            if (dr.GetValue(1).ToString() != getSubStr(1, sqlstr))
                            {   // direction
                                writeSql("update reactions set direction='" + dr.GetValue(1) + "' where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                update = true;
                            }
                            if (dr.GetValue(2).ToString() != getSubStr(2, sqlstr))
                            {   // remark
                                writeSql("update reactions set remark='" + dr.GetValue(2) + "' where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                update = true;
                            }
                            // clear if entry too old
                            if (!dr.IsDBNull(5))
                            {
                                DateTime dt1 = dr.GetDateTime(5);
                                DateTime dt2 = Convert.ToDateTime(timestampcorpassets);
                                if (dt2.Subtract(dt1).TotalHours > 6.0)
                                {
                                    writeSql("update reactions set cleared=0,clearedBy='',clearedTimestamp=null where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                    writeMySql("update silo set cleared=0,clearedBy='',clearedTimestamp=null where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                    update = true;
                                }
                            }
                            if (dr.GetValue(3).ToString() == "1" && getSubStr(3, sqlstr) == "True")
                            {
                                string dt1 = dr.GetDateTime(5).ToString("dd.MM.yyyy HH:mm:ss");
                                string dt2 = getSubStr(5, sqlstr);
                                if (dr.GetValue(4).ToString() != getSubStr(4, sqlstr) || (dt1 != dt2))
                                {
                                    writeSql("update reactions set cleared=1,clearedBy='" + dr.GetValue(4) + "',clearedTimestamp='" + dr.GetDateTime(5).ToString("yyyy-MM-dd HH:mm:ss") + "' where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                    update = true;
                                }
                            }
                            if (dr.GetValue(3).ToString() == "1" && getSubStr(3, sqlstr) == "False")
                            {   // silo cleared
                                writeSql("update reactions set cleared=1,clearedBy='" + dr.GetValue(4) + "',clearedTimestamp='" + dr.GetDateTime(5).ToString("yyyy-MM-dd HH:mm:ss") + "' where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                update = true;
                            }
                            if (dr.GetValue(3).ToString() == "0" && getSubStr(3, sqlstr) == "True")
                            {  
                                writeSql("update reactions set cleared=0,clearedBy='',clearedTimestamp=null where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid);
                                update = true;
                            }
                        }
                    } while (dr.Read());
                }
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
            }
            catch (MySqlException ex)
            {
                if (com != null)
                    com.Dispose();
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
                MessageBox.Show(ex.Message, "MySqlRead server values");
            }
            return update;
        }

        public bool setTowerReactions(long corpid)
        {   // delete old entries
            string sqlstr,strread,capacity,volume,qtyty,groupname;
            writeSql("delete from reactions where corpID=" + corpid + " and locationID not in (select moonID from apiStarbases)");
            writeSql("update reactions set output=0, input=0 where corpID=" + corpid);
            SQLiteConnection con = new SQLiteConnection();
            SQLiteCommand com = new SQLiteCommand(con);
            con.ConnectionString = connstr;
            try
            {   // fetch all silo reactor and harvester
                sqlstr = "begin;\r\n";
                con.Open();
                com.CommandText = "select itemID,locationID,typeID,groupID from apiAssets where (groupID=416 or groupID=438 or groupID=404) and depths=1 and flag=0 and locationID>=40000000 and locationID<60000000 and corpID=" + corpid;
                writeDebug("reader: "+com.CommandText);
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {   // dont overwrite values
                        groupname = strToSQLiteStr(readSql("select groupName from apiStarbases where moonID=" + dr.GetInt64(1) + " and corpID=" + corpid)); //set groupName for silo
                        capacity = readStatSql("select capacity from invTypes where typeID=" + dr.GetInt32(2));
                        if (readSql("select typeID from reactions where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid) == "")
                            sqlstr += "insert into reactions (itemID,locationID,corpID,typeID,groupID,capacity,cleared,groupName) values(" + dr.GetInt64(0) + "," + dr.GetInt64(1) + "," + corpid + "," + dr.GetInt32(2) + "," + dr.GetInt32(3) + "," + capacity + ",0,'" + groupname + "');\r\n";
                        else
                            sqlstr += "update reactions set locationID=" + dr.GetInt64(1) + ",typeID=" + dr.GetInt32(2) + ",groupID=" + dr.GetInt32(3) + ",capacity=" + capacity + ",groupName='" + groupname + "' where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid + ";\r\n";
                        // fetch whats inside if anything
                        strread = readSql("select typeID,quantity from apiAssets where parentItemID=" + dr.GetInt64(0));
                        if (strread != "")
                        {
                            volume = readStatSql("select volume from invTypes where typeID=" + getSubStr(0, strread));
                            volume = volume.Replace(',', '.');
                            sqlstr += "update reactions set contensTypeID=" + getSubStr(0, strread) + ",quantity=" + getSubStr(1, strread) + ",volume=" + volume + " where itemID=" + dr.GetInt64(0) + " and corpID=" + corpid + ";\r\n";
                        }
                    }
                    while (dr.Read());
                    sqlstr += "commit;\r\n";
                    writeSql(sqlstr);
                }
                dr.Close();

                // loop all reactors, find out which are sink and gain
                int quantity;
                string itemid;
                com.CommandText = "select locationID,contensTypeID,typeID from reactions where groupID=438 and corpID=" + corpid;
                writeDebug("reader: "+com.CommandText);
                dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {   // 
                        SQLiteConnection con2 = new SQLiteConnection();
                        SQLiteCommand com2 = new SQLiteCommand(con2);
                        con2.ConnectionString = connstrstat;
                        con2.Open();
                        com2.CommandText = "select typeID,quantity,input from invTypeReactions where reactionTypeID=" + dr.GetInt32(1);
                        writeDebug(com2.CommandText);
                        SQLiteDataReader dr2 = com2.ExecuteReader();
                        dr2.Read();
                        if (dr2.HasRows)
                        {
                            do
                            {   // loop all inputs and outputs of reaction
                                // fetch quantity and multiplier for quantity (sometimes quantity itself)
                                quantity = Convert.ToInt32(dr2.GetDouble(1));
                                qtyty = readStatSql("select valueInt from dgmTypeAttributes where attributeID=726 and typeID=" + dr2.GetInt32(0));
                                if (qtyty != "" && qtyty != "1")
                                    quantity *= Convert.ToInt32(qtyty);
                                else
                                {
                                    qtyty = readStatSql("select valueFloat from dgmTypeAttributes where attributeID=726 and typeID=" + dr2.GetInt32(0));
                                    if (qtyty != "" && qtyty != "1")
                                        quantity *= Convert.ToInt32(qtyty);
                                }
                                if (dr2.GetInt32(2) == 1)
                                {   // find silo with output assigned (silo will empty)
                                    itemid = readSql("select itemID from reactions where output=0 and direction='"+dirre+"' and locationID=" + dr.GetInt64(0) + " and contensTypeID=" + dr2.GetInt32(0) + " and corpID=" + corpid + " order by quantity");
                                    if (itemid != "")
                                        writeSql("update reactions set output=" + quantity + " where itemID=" + itemid, con);
                                    else
                                    {   
                                        itemid = readSql("select itemID from reactions where output=0 and locationID=" + dr.GetInt64(0) + " and contensTypeID=" + dr2.GetInt32(0) + " and corpID=" + corpid + " order by quantity");
                                        if (itemid != "")
                                            writeSql("update reactions set output=" + quantity + " where itemID=" + itemid, con);
                                    }
                                }
                                else
                                {   // find silo with input assigned (silo will fill)
                                    if (quantity == 100) // if its output to a silo from reaction it has double amount
                                        quantity = 200;
                                    itemid = readSql("select itemID from reactions where input=0 and direction='" + dirli + "' and locationID=" + dr.GetInt64(0) + " and contensTypeID=" + dr2.GetInt32(0) + " and corpID=" + corpid + " order by quantity desc");
                                    if (itemid != "")
                                        writeSql("update reactions set input=" + quantity + " where itemID=" + itemid, con);
                                    else
                                    {
                                        itemid = readSql("select itemID from reactions where input=0 and locationID=" + dr.GetInt64(0) + " and contensTypeID=" + dr2.GetInt32(0) + " and corpID=" + corpid + " order by quantity desc");
                                        if (itemid != "")
                                            writeSql("update reactions set input=" + quantity + " where itemID=" + itemid, con);
                                    }
                                }
                            }
                            while (dr2.Read());
                        }
                        dr2.Close();
                        dr2.Dispose();
                        con2.Close();
                        com2.Dispose();
                        con2.Dispose();
                    }
                    while (dr.Read());
                }
                dr.Close();
                
                // connect extractor to most likely silo
                com.CommandText = "select locationID from reactions where groupID=416 and corpID=" + corpid;
                writeDebug("reader: "+com.CommandText);
                dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {   // might have only harvester and a silo (applies to only harvestable stuff)
                        itemid = readSql("select itemID from reactions where contensTypeID>=16633 and contensTypeID<=16653 and input=0 and output=0 and groupID=404 and locationID=" + dr.GetInt64(0) + " and corpID=" + corpid + " order by capacity,quantity");
                        if(itemid!="")
                            writeSql("update reactions set input=100 where itemID=" + itemid, con);
                    }
                    while (dr.Read());
                }
                dr.Close();

                // silo daisy chain
                com.CommandText = "select itemID,locationID,contensTypeID,capacity,quantity from reactions where groupID=404 and input=0 and output=0 and contensTypeID>0 and corpID=" + corpid + " order by contensTypeID,locationID,capacity,quantity desc";
                writeDebug("reader: " + com.CommandText);
                dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {   // might have only harvester and silos (applies to only harvestable stuff)
                        itemid = readSql("select itemID from reactions where contensTypeID=" + dr.GetInt32(2) + " and (input>0 or output>0) and groupID=404 and locationID=" + dr.GetInt64(1) + " and corpID=" + corpid + " order by capacity");
                        if (itemid != "")
                            writeSql("update reactions set capacity=capacity+" + dr.GetInt32(3) + ",quantity=quantity+" + dr.GetInt32(4) + " where itemID=" + itemid, con);
                    }
                    while (dr.Read());
                }
                dr.Close();
                
                dr.Dispose();
                con.Close();
                com.Dispose();
                con.Dispose();

            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message, "SqlRead build processes");
            }

            return true;
        }

        public bool apiFetchPosDetail(long corpid, string keyid, string vcode)
        {
            string xmlstr, strline, sqlstr = "", typeid = "0", stront = "0", blockid = "0", blocks = "0", charterid = "0", charters = "0";
            long id;
            
            try
            {
                SQLiteConnection con = new SQLiteConnection();
                con.ConnectionString = connstr;
                SQLiteCommand com = new SQLiteCommand(con);
                con.Open();
                com.CommandText = "select itemID,locationID,typeID,systemID from apiAssets where groupID=365 and depths=1 and locationID>=40000000 and locationID<60000000 and corpID=" + corpid + " order by locationID,typeID";
                writeDebug("reader: "+com.CommandText);
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {   // get pos detail api
                        id = dr.GetInt64(0);
                        xmlstr = cachedApi(apiserver + "corp/StarbaseDetail.xml.aspx?keyID=" + keyid + "&vCode=" + vcode + "&itemID=" + id, "StarbaseDetail " + id);
                        /*if (xmlstr.Contains("<error code="))
                            MessageBox.Show(fetchXmlAttrib("<error code=", xmlstr), "Api corp/StarbaseDetail");
                        else if (xmlstr == "")
                            MessageBox.Show("Empty return", "Api corp/StarbaseDetail");*/

                        if (new_api_data || testdata || readSql("select fuelBlockQuantity from apiStarbases where itemID=" + dr.GetInt64(0)) == "0")
                        {
                            charters = "0"; charterid = "0"; stront = "0"; blocks = "0";
                            StringReader strread = new StringReader(xmlstr);
                            while ((strline = strread.ReadLine()) != null)
                            {
                                if (strline.Contains("<row "))
                                {
                                    typeid = fetchXmlValue("typeID=", strline);
                                    if (typeid == "16275")
                                        stront = fetchXmlValue("quantity=", strline);
                                    else if (typeid == "4051" || typeid == "4246" || typeid == "4247" || typeid == "4312")
                                    {
                                        blocks = fetchXmlValue("quantity=", strline);
                                        blockid = typeid;
                                    }
                                    else if (typeid == "24592" || typeid == "24593" || typeid == "24594" || typeid == "24595" || typeid == "24596" || typeid == "24597")
                                    {
                                        charters = fetchXmlValue("quantity=", strline);
                                        charterid = typeid;
                                    }
                                }
                            }
                            int hasSov = 1;
                            if (readSql("select factionID from apiSovereignty where solarSystemID=" + dr.GetInt32(3)) != "0") // npc faction
                                hasSov = 0;
                            if (dr.GetInt32(3) >= 31000000 && dr.GetInt32(3) < 31010000) // wormhole
                                hasSov = 0;
                            // write to db
                            sqlstr += "update apiStarbases set state=" + fetchXmlAttrib("<state>", xmlstr) + ",stateTimestamp='" + fetchXmlAttrib("<stateTimestamp>", xmlstr)
                                + "',onlineTimestamp='" + fetchXmlAttrib("<onlineTimestamp>", xmlstr) + "',usageFlags=" + fetchXmlAttrib("<usageFlags>", xmlstr)
                                + ",deployFlags=" + fetchXmlAttrib("<deployFlags>", xmlstr) + ",allowCorporationMembers=" + fetchXmlAttrib("<allowCorporationMembers>", xmlstr)
                                + ",allowAllianceMembers=" + fetchXmlAttrib("<allowAllianceMembers>", xmlstr) + ",ownerID=" + fetchXmlValue("ownerID=", xmlstr)
                                + ",onStandingDropStanding=" + fetchXmlValue("onStandingDrop standing=", xmlstr) + ",onStatusDropEnabled=" + fetchXmlValue("onStatusDrop enabled=", xmlstr)
                                + ",onStatusDropStanding=" + fetchXmlValue("\" standing=", xmlstr) + ",onAgression=" + fetchXmlValue("onAggression enabled=", xmlstr)
                                + ",onCorporationWar=" + fetchXmlValue("onCorporationWar enabled=", xmlstr) + ",fuelBlockTypeID=" + blockid + ",fuelBlockQuantity=" + blocks
                                + ",strontiumQuantity=" + stront + ",charterTypeID=" + charterid + ",charterQuantity=" + charters;
                            if (hasSov == 0)
                                sqlstr += ",hasSov=0";
                            sqlstr += " where itemID=" + id + ";\r\n";
                        }
                    }
                    while (dr.Read());
                }
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
                writeSql(sqlstr);
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message);
            }
            return false;
        }

        public bool apiFetchUniqueNames(bool corpflag, long charid, long corpid, string keyid, string vcode)
        {   // fetch unique item names and location from api including ships, container and pos structures, write into apiItems
            // coordinate unit 1=1 m
            if (corpflag) charid = 0;
            else corpid = 0;

            string sqlcmd, xmlstr, itemid, strline, subidlist;
            ArrayList items = new ArrayList();

            try
            {   // get all container(deployable, 14=Corpse), ships, pos structures that we could need names from (and the position in space)
                //if(corpflag)
                    sqlcmd = "select itemID from apiAssets where (typeID=2233 or groupID=12 or groupID=14 or groupID=340 or groupID=448 or groupID=649 or (categoryID=23 and locationID<60000000))"
                    + " and singleton=1 and charID=" + charid + " and corpID=" + corpid + " and itemID not in (select itemID from apiItems)";
                //else sqlcmd = "select itemID from apiAssets where (typeID=2233 or groupID=12 or groupID=14 or groupID=340 or groupID=448 or groupID=649 or categoryID=6 or (categoryID=23 and locationID<60000000))"
                //    + " and singleton=1 and charID=" + charid + " and corpID=" + corpid + " and itemID not in (select itemID from apiItems)";
                SQLiteConnection con = new SQLiteConnection();
                con.ConnectionString = connstr;
                SQLiteCommand com = new SQLiteCommand(con);
                com.CommandText = sqlcmd;
                writeDebug("reader: "+com.CommandText);
                con.Open();
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    do
                    {   // build item list for api query
                        itemid = dr.GetValue(0).ToString();
                        if (itemid != "" && itemid != null)
                            items.Add(itemid);
                    }
                    while (dr.Read());
                }
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message, "Sql apiFetchUniqueNames");
                return false;
            }
            // start refreshing names from api
            sqlcmd = "begin;\r\n";
            // get the list (max 250)
            int i = 0, bulk = 100;
            if (testdata) bulk = 1;
            while (i < items.Count)
            {   // cut itemlist to peaces of 100
                int j = 0;
                subidlist = "";
                while (j < bulk && (i + j) < items.Count) // for test ask only 1
                {
                    if (j > 0)
                        subidlist += ",";
                    subidlist += items[i + j].ToString();
                    j++;
                }
                i += j;
                if (subidlist.Length == 0)
                    return true;
                // fetch api with item names and location
                string apistr;
                if (corpid > 0)
                    apistr = apiserver + "corp/Locations.xml.aspx?keyID=" + keyid + "&vCode=" + vcode + "&IDs=" + subidlist;
                else
                    apistr = apiserver + "char/Locations.xml.aspx?keyID=" + keyid + "&vCode=" + vcode + "&characterID=" + charid + "&IDs=" + subidlist;
                if(testdata)
                    xmlstr = cachedApi(apistr,"Location " + charid + " " + corpid + " " + subidlist);
                else xmlstr = fetchApi(apistr);
                // check for api error
                bool skip=false;
                if (xmlstr.Contains("<error code="))
                {
                    //MessageBox.Show("IDs:" + subidlist + " " + fetchXmlAttrib("<error code=", xmlstr), "Api Locations");
                    if (bulk > 1)
                    {   // Fallback, repeat and fetch only 1 dataset per request
                        writeDebug("fetch unique names fallback");
                        i = j = 0;
                        bulk = 1;
                        subidlist = "";
                        sqlcmd = "begin;\r\n";
                    }
                    skip=true;
                }
                else if (xmlstr == "")
                {
                    //MessageBox.Show("Empty return", "Api Locations");
                    if (bulk > 1)
                    {   // Fallback, repeat and fetch only 1 dataset per request
                        writeDebug("fetch unique names fallback");
                        i = j = 0;
                        bulk = 1;
                        subidlist = "";
                        sqlcmd = "begin;\r\n";
                    }
                    skip = true;
                }
                // write it to db
                string itemname = "", groupid, systemid, tstr;
                int k = 0;
                StringReader strread = new StringReader(xmlstr);
                while (!skip&&(strline = strread.ReadLine()) != null)
                {   // fetch next line
                    if (strline.Contains("<row "))
                    {   // line with data
                        itemid = fetchXmlValue("itemID=", strline);
                        itemname = fetchXmlValue("itemName=", strline);
                        itemname = strToSQLiteStr(itemname);
                        if (itemid != "" && itemname != "")
                        {   // remove decimal glitch of coords
                            string x = fetchXmlValue(" x=", strline);
                            if (x.Contains("."))
                                x = x.Substring(0, x.IndexOf("."));
                            string y = fetchXmlValue(" y=", strline);
                            if (y.Contains("."))
                                y = y.Substring(0, y.IndexOf("."));
                            string z = fetchXmlValue(" z=", strline);
                            if (z.Contains("."))
                                z = z.Substring(0, z.IndexOf("."));

                            tstr = readSql("select groupID,systemID from apiAssets where itemID=" + itemid);
                            groupid = getSubStr(0, tstr);
                            systemid = getSubStr(1, tstr);

                            // itemID,itemName,userID,corpID,x,y,z,groupID,systemID,trash
                            sqlcmd += "replace into apiItems values(" + itemid + ",'" + itemname + "'," + charid + "," + corpid + "," + groupid + "," + systemid + "," + x + "," + y + "," + z + ",0);\r\n";
                            k++;
                        }
                    }
                }
                /*if (k != j)
                    MessageBox.Show("requested:" + j + " answer:" + k, "Api Locations");*/
            }
            sqlcmd += "commit;";
            writeSql(sqlcmd);
            // delete all (old) items from item/location
            writeSql("delete from apiItems where itemID not in (select itemID from apiAssets);");
            return true;
        }

        public bool assetsToDb(string xml, bool corpflag, long charid, long corpid)
        {
            string gid, cid;
            if (corpflag) charid = 0;
            else corpid = 0;

            // delete cached prices
            if (new_api_data)
            {
                foreach (FileInfo f in new DirectoryInfo(filepath).GetFiles("Isk*.xml"))
                {
                    f.Delete();
                }
            }

            if (!new_api_data || testdata)
            {   // if itemcount doesnt match get anyways
                if (!testdata && countRow(xml) == Convert.ToInt32(readSql("select count(itemID) from apiAssets where corpID=" + corpid + " and charID="+charid)))
                    return true;
                else
                {
                    new_api_data = true;
                    updatereallocation = true;
                }
            }
            else updatereallocation = true;
            int depths = 0;
            string[] parentid = new string[10] { "0", "0", "0", "0", "0", "0", "0", "0", "0", "0" };
            StringReader strread = new StringReader(xml);
            writeSql("update apiAssets set trash=1 where charID=" + charid + " and corpID=" + corpid);
            string strline, locationid = "", rawq = "", sqlcmd = "begin;\r\n";
            while ((strline = strread.ReadLine()) != null)
            {   // every item has a locationid of the upmost container which is station or pos, every item in a container carries the parentItemID which identifies the current container itemID
                if (strline.Contains("<rowset "))
                {
                    depths++; // a container deeper
                }
                else if (strline.Contains("</rowset>"))
                {
                    depths--; // a container up
                }
                if (depths == 1 && strline.Contains("locationID=")) // store root location
                    locationid = fetchXmlValue("locationID=", strline);
                if (depths > 0 && strline.Contains("itemID=")) // store parent id
                    parentid[depths] = fetchXmlValue("itemID=", strline);
                int typeid, groupid, categoryid;
                if (strline.Contains("<row "))
                {   // line with data: itemID,charID,corpID,locationID,systemID,typeID,quantity,groupID,categoryID,depths,parentItemID,flag,singleton,rawQuantity,trash
                    strline = strToSQLiteStr(strline);
                    // not every line has this attrib (for bpo/bpc)
                    if (fetchXmlValue("rawQuantity=", strline) == "")
                        rawq = "0";
                    else rawq = fetchXmlValue("rawQuantity=", strline);
                    // add group and category
                    typeid = Convert.ToInt32(fetchXmlValue("typeID=", strline));
                    gid = readStatSql("select groupID from invTypes where typeID=" + typeid);
                    if (gid != "")
                        groupid = Convert.ToInt32(gid);
                    else groupid = 0;
                    cid = readStatSql("select categoryID from invGroups where groupID=" + groupid);
                    if (cid != "")
                        categoryid = Convert.ToInt32(cid);
                    else categoryid = 0;
                    // set station id if its an office id
                    long locid = Convert.ToInt64(locationid);
                    if (locid >= 66000000 && locid <= 66014933)
                        locid -= 6000001;
                    else if (locid >= 66000000)
                        locid -= 6000000;
                    if(typeid != 0)
                        sqlcmd += "replace into apiAssets values(" + fetchXmlValue("itemID=", strline) + "," + charid + "," + corpid + "," + locid + "," + locid + "," + typeid + "," + fetchXmlValue("quantity=", strline)
                            + "," + groupid + "," + categoryid + "," + depths + "," + parentid[depths - 1] + "," + fetchXmlValue("flag=", strline) + "," + fetchXmlValue("singleton=", strline) + "," + rawq + ",0);\r\n";
                }
            }
            sqlcmd += "commit;\r\n";
            writeSql(sqlcmd);
            writeSql("delete from apiAssets where trash=1 and charID=" + charid + " and corpID=" + corpid);
            writeSql("delete from apiItems where charID=" + charid + " and corpID=" + corpid);
            
            return true;
        }

        public int countRow(string xml)
        {   // count lines with data
            int row = 0;
            StringReader strread = new StringReader(xml);
            string strline;
            while ((strline = strread.ReadLine()) != null)
            {   
                if (strline.Contains("<row "))
                    row++;
            }
            return row;
        }

        public bool setRealLocation(bool corpflag, long charid, long corpid)
        {
            if (!updatereallocation) return true;
            string sqlcmd, locationid;
            long id;

            try
            {   // get all tower in space from assets
                sqlcmd = "select itemID from apiAssets where groupID=365 and depths=1 and systemID<40000000 and singleton=1 and corpID=" + corpid;
                SQLiteConnection con = new SQLiteConnection();
                con.ConnectionString = connstr;
                SQLiteCommand com = new SQLiteCommand(con);
                com.CommandText = sqlcmd;
                writeDebug("reader: "+com.CommandText);
                con.Open();
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    sqlcmd = "begin;\r\n";
                    do
                    {   // set moonid as location
                        id = dr.GetInt64(0);
                        locationid = readSql("select moonID from apiStarbases where itemID=" + id);
                        if (locationid != "")
                        {
                            sqlcmd += "update apiAssets set locationID=" + locationid + " where itemID=" + id + ";\r\n";
                            //else MessageBox.Show(dr.GetValue(0).ToString());
                        }
                    }
                    while (dr.Read());
                    sqlcmd += "commit;";
                    writeSql(sqlcmd);
                }
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message, "Sql setRealLocation");
                return false;
            }

            try
            {   // get all structures near the tower and set parentItem=tower and location=moon
                sqlcmd = "select itemID,systemID from apiAssets where categoryID=23 and groupID<>365 and depths=1 and systemID<40000000 and singleton=1 and corpID=" + corpid;
                SQLiteConnection con = new SQLiteConnection();
                con.ConnectionString = connstr;
                SQLiteCommand com = new SQLiteCommand(con);
                com.CommandText = sqlcmd;
                con.Open();
                writeDebug("reader: " + com.CommandText);
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    sqlcmd = "begin;\r\n";
                    do
                    {   // set moonid as location and towerid as parentitemid
                        id = dr.GetInt64(0);
                        long towerid = getLocationByTower(id, dr.GetInt64(1));

                        if (towerid != 0)
                        {
                            locationid = readSql("select moonID from apiStarbases where itemID=" + towerid);
                            if (locationid != "")
                            {   // set pos module location=moon where the tower is
                                sqlcmd += "update apiAssets set locationID=" + locationid + ",parentItemID=" + towerid + " where itemID=" + id + ";\r\n";
                            }
                            //else MessageBox.Show(dr.GetValue(0).ToString());
                        }
                    }
                    while (dr.Read());
                    sqlcmd += "commit;";
                    writeSql(sqlcmd);
                }
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message, "Sql setRealLocation");
                return false;
            }

            try
            {   // get all other items which maybe near a pos
                sqlcmd = "select itemID,systemID from apiAssets where categoryID<>23 and groupID<>365 and depths=1 and locationID<40000000";
                if (corpflag) sqlcmd += " and corpID=" + corpid;
                else sqlcmd += " and charID=" + charid;
                SQLiteConnection con = new SQLiteConnection();
                con.ConnectionString = connstr;
                SQLiteCommand com = new SQLiteCommand(con);
                com.CommandText = sqlcmd;
                writeDebug("reader: " + com.CommandText);
                con.Open();
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    sqlcmd = "begin;\r\n";
                    do
                    {   // set moonid as location and towerid as parentitemid
                        id = dr.GetInt64(0);
                        long towerid = getLocationByTower(id, dr.GetInt64(1));

                        if (towerid != 0)
                        {
                            locationid = readSql("select moonID from apiStarbases where itemID=" + towerid);
                            if (locationid != "")
                            {   // set root location for all other items
                                sqlcmd += "update apiAssets set locationID=" + locationid + " where itemID=" + id + ";\r\n";
                                //else MessageBox.Show(dr.GetValue(0).ToString());
                            }
                        }
                    }
                    while (dr.Read());
                    sqlcmd += "commit;";
                    writeSql(sqlcmd);
                }
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message, "Sql setRealLocation");
                return false;
            }

            for (int i = 2; i < 10; i++)
            {
                try
                {   // get all other items inside one of the above
                    sqlcmd = "select itemID,parentItemID from apiAssets where depths=" + i + " and locationID<40000000";
                    if (corpflag) sqlcmd += " and corpID=" + corpid;
                    else sqlcmd += " and charID=" + charid;
                    SQLiteConnection con = new SQLiteConnection();
                    con.ConnectionString = connstr;
                    SQLiteCommand com = new SQLiteCommand(con);
                    com.CommandText = sqlcmd;
                    writeDebug("reader: " + com.CommandText);
                    con.Open();
                    SQLiteDataReader dr = com.ExecuteReader();
                    dr.Read();
                    if (dr.HasRows)
                    {
                        sqlcmd = "begin;\r\n";
                        do
                        {   // get locationid from the container
                            locationid = readSql("select locationID from apiAssets where itemID=" + dr.GetInt64(1));
                            if (locationid != "" && !sqlcmd.Contains(dr.GetInt64(1).ToString()))
                            {   // set root location from parent
                                sqlcmd += "update apiAssets set locationID=" + locationid + " where depths=" + i + " and parentitemID=" + dr.GetInt64(1) + ";\r\n";
                                //sqlcmd += "update apiAssets set locationID=" + locationid + " where itemID=" + dr.GetInt64(0) + ";\r\n";
                                //else MessageBox.Show(dr.GetValue(0).ToString());
                            }
                        }
                        while (dr.Read());
                        sqlcmd += "commit;";
                        writeSql(sqlcmd);
                    }
                    dr.Close();
                    dr.Dispose();
                    com.Dispose();
                    con.Close();
                    con.Dispose();
                }
                catch (SQLiteException ex)
                {
                    MessageBox.Show(ex.Message, "Sql setRealLocation");
                    return false;
                }
            }

            return true;
        }

        public long getLocationByTower(long itemid, long systemid)
        {   // get nearest tower of given named item
            double x = 0, y = 0, z = 0, d = 400000;
            string ret, sqlstr;

            ret = readSql("select x,y,z from apiItems where itemID=" + itemid);
            if (ret == "") return 0;
            x = Convert.ToDouble(getSubStr(0, ret));
            y = Convert.ToDouble(getSubStr(1, ret));
            z = Convert.ToDouble(getSubStr(2, ret));
            if (x == 0 || y == 0 || z == 0) return 0;

            sqlstr = "select itemID from apiItems where groupID=365 and systemID=" + systemid + " and x>" + (x - d) + " and x<" + (x + d) + " and y>" + (y - d) + " and y<" + (y + d) + " and z>" + (z - d) + " and z<" + (z + d);
            string towerid = readSql(sqlstr);
            if (towerid != "")
                return Convert.ToInt64(towerid);
            return 0;
        }

        public bool setCorpHangars(string xml)
        {
            StringReader strread = new StringReader(xml);
            string strline;
            int j = 0;
            while ((strline = strread.ReadLine()) != null)
            {
                if (strline.Contains("<row "))
                {   // line with data
                    if (j < 7)
                        chs[j] = fetchXmlValue(" description=", strline);
                    j++;
                }
            }
            return true;
        }

        public string hangarname(short flag,short depths, long parentid)
        {
            string ret="",typeid;
            if (depths == 3)
            {
                parentid = Convert.ToInt64(readSql("select parentItemID from apiAssets where itemID=" + parentid));
                ret = readStatSql("select flagText from invFlags where flagID=" + readSql("select flag from apiAssets where itemID=" + parentid));
            }
            else if (depths > 2)
                ret = readStatSql("select flagText from invFlags where flagID=" + readSql("select flag from apiAssets where itemID=" + parentid));
            else
                ret = readStatSql("select flagText from invFlags where flagID=" + flag);
            if (ret == "Corp Security Access Group 2")
                ret = chs[1];
            if (ret == "Corp Security Access Group 3")
                ret = chs[2];
            if (ret == "Corp Security Access Group 4")
                ret = chs[3];
            if (ret == "Corp Security Access Group 5")
                ret = chs[4];
            if (ret == "Corp Security Access Group 6")
                ret = chs[5];
            if (ret == "Corp Security Access Group 7")
                ret = chs[6];
            typeid = readSql("select typeID from apiAssets where itemID=" + parentid);
            if (ret == "Hangar" && depths > 1 && (typeid == "17621" || typeid == "27"))
                ret = chs[0];
            return ret;
        }

        public bool outpostsToDb(string xml)
        {
            if (!new_api_data)
            {
                if (countRow(xml) == Convert.ToInt32(readSql("select count(stationID) from apiOutposts")))
                    return true;
            }
            StringReader strread = new StringReader(xml);
            string strline, sqlcmd = "begin;\r\n";
            while ((strline = strread.ReadLine()) != null)
            {
                if (strline.Contains("<row "))
                {   // line with data: stationID,stationName,stationTypeID,solarSystemID,corporationID,corporationName,trash
                    strline = strToSQLiteStr(strline);
                    sqlcmd += "replace into apiOutposts values(" + fetchXmlValue("stationID=", strline) + ",'" + strToSQLiteStr(fetchXmlValue("stationName=", strline)) + "'," + fetchXmlValue("stationTypeID=", strline)
                        + "," + fetchXmlValue("solarSystemID=", strline) + "," + fetchXmlValue("corporationID=", strline) + ",'" + strToSQLiteStr(fetchXmlValue("corporationName=", strline)) + "',0);\r\n";
                }
            }
            sqlcmd += "commit;\r\n";
            if (writeSql(sqlcmd) < 1)
                return false;
            return true;
        }

        public bool starbasesToDb(string xml, long corpid)
        {
            if (!new_api_data && !testdata)
            {
                if (!testdata && countRow(xml) == Convert.ToInt32(readSql("select count(itemID) from apiStarbases where corpID=" + corpid)))
                    return true;
                else updatereallocation = true;
            }
            else updatereallocation = true;
            StringReader strread = new StringReader(xml);
            writeSql("update apiStarbases set trash=1 where corpID=" + corpid);
            string sqlcmd = "begin;\r\n", strline, mname, tname, blockcon, strontcon, owner, sov, fold, str, fuelbaysize, strontbaysize;

            while ((strline = strread.ReadLine()) != null)
            {
                if (strline.Contains("<row "))
                {   // line with data: itemID,corpID,typeID,locationID,moonID,state,stateTimestamp,onlineTimestamp,standingOwnerID,trash,...
                    strline = strToSQLiteStr(strline);
                    mname = strToSQLiteStr(readStatSql("select itemName from invNames where itemID=" + fetchXmlValue("moonID=", strline)));
                    tname = strToSQLiteStr(getItemName(Convert.ToInt64(fetchXmlValue("itemID=", strline))));
                    blockcon = readStatSql("select quantity from invControlTowerResources where controlTowerTypeID=" + fetchXmlValue("typeID=", strline) + " and (resourceTypeID=4051 or resourceTypeID=4246 or resourceTypeID=4247 or resourceTypeID=4312)");
                    strontcon = readStatSql("select quantity from invControlTowerResources where controlTowerTypeID=" + fetchXmlValue("typeID=", strline)+" and resourceTypeID=16275");
                    fuelbaysize = readStatSql("select capacity from invTypes where typeID=" + fetchXmlValue("typeID=", strline));
                    strontbaysize = readStatSql("select valueFloat from dgmTypeAttributes where attributeID=1233 and typeID=" + fetchXmlValue("typeID=", strline));
                    // keep old values
                    str = readSql("select groupName,fold,hasSov from apiStarbases where itemID=" + fetchXmlValue("itemID=", strline) + " and corpID=" + corpid);
                    if (str != "")
                    {
                        owner = getSubStr(0, str);
                        fold = getSubStr(1, str);
                        sov = getSubStr(2, str);
                    }
                    else
                    {
                        owner = readUserSql("select name from corp where corpID=" + corpid);
                        fold = "6";
                        sov = "True";
                    }
                    if (sov == "True" || sov == "") sov = "1";
                    else sov = "0";
                    // replace
                    sqlcmd += "replace into apiStarbases (itemID,corpID,typeID,locationID,moonID,state,capacityPrimary,capacitySecondary,stateTimestamp,onlineTimestamp,standingOwnerID,moonName,towerName,fuelBlockConsumption,strontiumConsumption,hasSov,groupName,fold) values("
                        + fetchXmlValue("itemID=", strline) + "," + corpid + "," + fetchXmlValue("typeID=", strline) + "," + fetchXmlValue("locationID=", strline)
                        + "," + fetchXmlValue("moonID=", strline) + "," + fetchXmlValue("state=", strline) + "," + fuelbaysize + "," + strontbaysize
                        + ",'" + fetchXmlValue("stateTimestamp=", strline) + "','" + fetchXmlValue("onlineTimestamp=", strline) + "'," + fetchXmlValue("standingOwnerID=", strline) 
                        + ",'" + mname + "','" + tname + "'," + blockcon + "," + strontcon + "," + sov + ",'" + strToSQLiteStr(owner) + "','"+fold+"');\r\n";
                }
            }
            sqlcmd += "commit;\r\n";
            if (writeSql(sqlcmd) < 1)
                return false;
            writeSql("delete from apiStarbases where trash=1 and corpID=" + corpid);
            return true;
        }

        public bool apiFetchSovereignity()
        {
            string urlstr = apiserver + "map/Sovereignty.xml.aspx";
            string xmlstr = cachedApi(urlstr, "Sovereignty");
            if (!new_api_data || testdata)
            {
                if (countRow(xmlstr) == Convert.ToInt32(readSql("select count(solarSystemID) from apiSovereignty")))
                    return true;
            }
            
            StringReader strread = new StringReader(xmlstr);
            string sqlcmd = "begin;\r\n", strline;
            while ((strline = strread.ReadLine()) != null)
            {
                if (strline.Contains("<row "))
                {   // line with data: solarSystemID,allianceID,factionID,(solarSystemName),corporationID
                    strline = strToSQLiteStr(strline);
                    // replace
                    sqlcmd += "replace into apiSovereignty (solarSystemID,allianceID,factionID,corporationID) values("
                        + fetchXmlValue("solarSystemID=", strline) + "," + fetchXmlValue("allianceID=", strline) + "," + fetchXmlValue("factionID=", strline) + "," + fetchXmlValue("corporationID=", strline) + ");\r\n";
                }
            }
            sqlcmd += "commit;\r\n";
            if (writeSql(sqlcmd) < 1)
                return false;
            return true;
        }

        public string getItemName(long itemid)
        {
            string tname, uname, typeid, categoryid;
            categoryid = readSql("select typeID,categoryID from apiAssets where itemID=" + itemid);
            if (categoryid == "")
                return "";
            typeid = categoryid.Substring(0, categoryid.IndexOf("|"));
            categoryid = categoryid.Substring(categoryid.IndexOf("|") + 1);
            tname = readStatSql("select typeName from invTypes where typeID=" + typeid);
            uname = readSql("select itemName from apiItems where itemID=" + itemid);
            if (uname.Contains(tname))
                return uname;
            if (uname != "")
                tname += " - " + uname;
            return tname;
        }

        public string cachedApi(string urlstr, string filename)
        { // fetch api from file if too old/ not exist fetch from url and write to file
            string xmlstr, xmlfile, timestamp;
            new_api_data = false;
            try
            {
                using (StreamReader sr = new StreamReader(filepath + "\\" + filename+".xml"))
                {   // read file
                    xmlfile = sr.ReadToEnd();
                    sr.Close();
                    timestamp = fetchXmlAttrib("<cachedUntil", xmlfile);
                    if (timestamp.Length == 19)
                    {   // too old?
                        DateTime dt = Convert.ToDateTime(timestamp);
                        TimeSpan ts = dt.Subtract(DateTime.UtcNow);
                        if (ts.TotalMinutes < 0.0 && !testdata)
                        {
                            xmlstr = fetchApi(urlstr);
                            if (xmlstr != "" && !xmlstr.Contains("<error code="))
                            {
                                StreamWriter sw = new StreamWriter(filepath + "\\" + filename + ".xml");
                                sw.Write(xmlstr);
                                sw.Close();
                                new_api_data = true;
                                return xmlstr;
                            }
                            else
                            {
                                apiErrors++;
                                return xmlstr;
                            }
                        }
                    }
                    else if(!urlstr.Contains("eve-central"))
                    {
                        xmlstr = fetchApi(urlstr);
                        if (xmlstr != "" && !xmlstr.Contains("<error code="))
                        {
                            StreamWriter sw = new StreamWriter(filepath + "\\" + filename + ".xml");
                            sw.Write(xmlstr);
                            sw.Close();
                            new_api_data = true;
                        }
                        return xmlstr;
                    }
                }
            }
            catch (FileNotFoundException)
            {   // not exist
                xmlstr = fetchApi(urlstr);
                if (xmlstr != "" && !xmlstr.Contains("<error code="))
                {
                    StreamWriter sw = new StreamWriter(filepath + "\\" + filename + ".xml");
                    sw.Write(xmlstr);
                    sw.Close();
                    new_api_data = true;
                }
                return xmlstr;
            }
            return xmlfile;
        }

        public string hoursToDHstr(double hours)
        {
            return TimeSpan.FromHours(hours).Days + "d " + TimeSpan.FromHours(hours).Hours.ToString("00") + "h";
        }

        internal Image customProgressBar(string text, double barlength, double barsize, Color barcol, Color bg, int width, int height, bool righttoleft, string font)
        {
            Image img = new Bitmap(width, height, System.Drawing.Imaging.PixelFormat.Format32bppArgb);
            if (barlength > 1.0) barlength = 1.0;
            if (barlength < 0.0) barlength = 0.0;
            if (barsize > 1.0) barsize = 1.0;
            if (barsize < 0.0) barsize = 0.0;
            if (height < 3) height = 3;
            using (Graphics g = Graphics.FromImage(img))
            {
                g.Clear(bg);
                if (righttoleft)
                {
                    g.FillRectangle(new SolidBrush(barcol), Convert.ToInt32(((1.0 - (barsize * barlength)) * (width - 2.0))) + 1, 1, Convert.ToInt32(Math.Round(barsize * barlength * (width - 2.0),MidpointRounding.AwayFromZero)), height - 2);
                    g.DrawRectangle(new Pen(Color.Gray), Convert.ToInt32((1.0 - barsize) * (width - 1.0)), 0, Convert.ToInt32(barsize * (width - 1.0)), height - 1);
                }
                else
                {
                    g.FillRectangle(new SolidBrush(barcol), 1, 1, Convert.ToInt32(barsize * barlength * (width - 2.0)), height - 2);
                    g.DrawRectangle(new Pen(Color.Gray), 0, 0, Convert.ToInt32(barsize * (width - 1.0)), height - 1);
                }
                float wi, hi;
                using (System.Drawing.Graphics graphics = System.Drawing.Graphics.FromImage(new Bitmap(1, 1)))
                {
                    SizeF size = graphics.MeasureString(text, new Font(font, (float)(height / 2.0), FontStyle.Regular, GraphicsUnit.Point));
                    wi = size.Width;
                    hi = size.Height;
                }
                Font df = new Font(font, (float)(height / 2.0));
                if(righttoleft) g.DrawString(text, df, new SolidBrush(Color.Black), (float)(((width + ((1.0 - barsize) * width)) / 2.0) - (wi / 2.0)), (float)((height - hi) / 2.0));
                else g.DrawString(text, df, new SolidBrush(Color.Black), (float)((width * barsize / 2.0) - (wi / 2.0)), (float)((height - hi) / 2.0));
            }
            return img;
        }

        public string Encrypt(string toEncrypt, string key)
        {
            try
            {
                byte[] keyArray;
                byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);

                //System.Windows.Forms.MessageBox.Show(key);
                //get hashcode regards to your key
                MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
                keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
                //Always release the resources and flush data
                // of the Cryptographic service provide. Best Practice

                hashmd5.Clear();

                TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
                //set the secret key for the tripleDES algorithm
                tdes.Key = keyArray;
                //mode of operation. there are other 4 modes.
                //We choose ECB(Electronic code Book)
                tdes.Mode = CipherMode.ECB;
                //padding mode(if any extra byte added)

                tdes.Padding = PaddingMode.PKCS7;

                ICryptoTransform cTransform = tdes.CreateEncryptor();
                //transform the specified region of bytes array to resultArray
                byte[] resultArray =
                  cTransform.TransformFinalBlock(toEncryptArray, 0,
                  toEncryptArray.Length);
                //Release resources held by TripleDes Encryptor
                tdes.Clear();
                //Return the encrypted data into unreadable string format
                return Convert.ToBase64String(resultArray, 0, resultArray.Length);
            }
            catch (CryptographicException ex)
            {
                MessageBox.Show("Unable to encrypt: " + ex.Message, "Error");
                return "";
            }
        }
        public string Decrypt(string cipherString, string key)
        {
            try
            {
                byte[] keyArray;
                //get the byte code of the string

                byte[] toEncryptArray = Convert.FromBase64String(cipherString);

                //if hashing was used get the hash code with regards to your key
                MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
                keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
                //release any resource held by the MD5CryptoServiceProvider
                hashmd5.Clear();

                TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
                //set the secret key for the tripleDES algorithm
                tdes.Key = keyArray;
                //mode of operation. there are other 4 modes. 
                //We choose ECB(Electronic code Book)

                tdes.Mode = CipherMode.ECB;
                //padding mode(if any extra byte added)
                tdes.Padding = PaddingMode.PKCS7;

                ICryptoTransform cTransform = tdes.CreateDecryptor();
                byte[] resultArray = cTransform.TransformFinalBlock(
                                     toEncryptArray, 0, toEncryptArray.Length);
                //Release resources held by TripleDes Encryptor                
                tdes.Clear();
                //return the Clear decrypted TEXT
                return UTF8Encoding.UTF8.GetString(resultArray);
            }
            catch (CryptographicException ex)
            {
                MessageBox.Show("Unable to decrypt: " + ex.Message, "Error");
                return "";
            }
        }

        public string fetchApi(string urlstr)
        { // fetch api, return as string
            string xmlstr="XML empty";
            try
            {
                WebClient myClient = new WebClient();
                Encoding utf8 = Encoding.UTF8;
                myClient.Encoding = utf8;
                xmlstr = myClient.DownloadString(urlstr);

                if (urlstr.Contains("?") && !urlstr.Contains("eve-central"))
                    writeDebug(urlstr.Substring(0,urlstr.IndexOf("?"))+"...");
                else writeDebug(urlstr);
                writeDebug(xmlstr);
                return xmlstr;
            }
            catch (WebException ex)
            {
                //MessageBox.Show(ex.Message, "WebExeption");
                writeDebug(urlstr);
                writeDebug(xmlstr);
                apiErrors++;
                return "";
            }
        }

        public double fetchPrice(int typeid, bool wts)
        {
            string ret = "1.0",test="1.1";
            double price,dtest;
            string xmlstr = cachedApi("http://api.eve-central.com/api/marketstat?typeid=" + typeid, "Isk " + typeid); //+ "&usesystem=" + isksystem, "Isk " + isksystem + " " + typeid);
            if (xmlstr.Contains("<buy>") && wts)
                ret = fetchXmlAttrib("<percentile", xmlstr);
            if (xmlstr.Contains("<sell>") && !wts)
            {
                xmlstr = xmlstr.Substring(xmlstr.IndexOf("<sell>"));
                ret = fetchXmlAttrib("<percentile", xmlstr);
            }
            dtest = Convert.ToDouble(test);
            if (dtest.ToString() != test) // check for localization
                ret = ret.Replace(".", ",");
            if (ret.Length > 2)
                ret = ret.Trim();
            price = Math.Round(Convert.ToDouble(ret), 2); 
            return price;
        }

        public string fetchXmlAttrib(string search, string xml)
        {   // get <search>value</search> and return value as string
            string val;
            if (xml.Contains(search))
            {
                val = xml.Substring(xml.IndexOf(search) + search.Length - 1);
                if (val.Contains(">"))
                {
                    val = val.Substring(val.IndexOf(">") + 1);
                    if (val.Contains("</"))
                    {
                        val = val.Substring(0, val.IndexOf("</"));
                        return val;
                    }
                }
            }
            return "";
        }

        public string fetchXmlValue(string search, string xml)
        {   // get search="value" and return value as string
            string val;
            if (xml.Contains(search))
            {
                val = xml.Substring(xml.IndexOf(search) + search.Length);
                if (val.Contains("\""))
                {
                    val = val.Substring(val.IndexOf("\"") + 1);
                    if (val.Contains("\""))
                    {
                        val = val.Substring(0, val.IndexOf("\""));
                        return val;
                    }
                    else return "";
                }
                else return "";
            }
            else return "";
        }

        public string strToSQLiteStr(string str)
        {   // translate special chars for sql statement
            str = str.Replace("&amp;lt;", "<"); // correct xml retranslation glitch
            str = str.Replace("&amp;gt;", ">"); // correct xml retranslation glitch
            str = str.Replace("'", "''"); // double ' to untag against sql string delimiter
            return str;
        }
        public string strToMysqlStr(string str)
        {   // translate special chars for sql statement
            str = str.Replace("&amp;lt;", "<"); // correct xml retranslation glitch
            str = str.Replace("&amp;gt;", ">"); // correct xml retranslation glitch
            str = str.Replace("'", "\\'"); // double ' to untag against sql string delimiter
            return str;
        }
        public string getSubStr(int idx, string str)
        {
            string cpy = str;
            for (int i = 0; i < idx; i++)
            {
                if (!cpy.Contains("|"))
                {
                    MessageBox.Show("Substring index too high", "GetSubString");
                    return "";
                }
                else cpy = cpy.Substring(cpy.IndexOf("|") + 1);
            }
            if (cpy.Contains("|"))
                return cpy.Substring(0, cpy.IndexOf("|"));
            else return cpy;
        }

        public int writeSql(string sqlcmd)
        {   // SQL write command, returns rows affected
            int ret = 0;
            if (sqlcmd == "")
                return 0;
            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = connstr;
            SQLiteCommand com = new SQLiteCommand(con);
            try
            {
                con.Open();
                com.CommandText = sqlcmd;
                ret = com.ExecuteNonQuery();
                com.Dispose();
                con.Close();
                con.Dispose();
                writeDebug(sqlcmd + " rows:" + ret);
            }
            catch (SQLiteException ex)
            {
                if (com != null)
                    com.Dispose();
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
                if (sqlcmd.Length > 500)
                    sqlcmd = sqlcmd.Substring(0, 500);
                MessageBox.Show(ex.Message + "\r\n" + sqlcmd, "SqlWrite");
            }
            return ret;
        }
        public int writeSql(string sqlcmd, SQLiteConnection con)
        {   // SQL write command reusing connection, returns rows affected
            int ret = 0;
            if (sqlcmd == "")
                return 0;
            SQLiteCommand com = new SQLiteCommand(con);
            try
            {
                com.CommandText = sqlcmd;
                ret = com.ExecuteNonQuery();
                com.Dispose();
                writeDebug(sqlcmd + " rows:" + ret);
            }
            catch (SQLiteException ex)
            {
                if (com != null)
                    com.Dispose();
                if (sqlcmd.Length > 500)
                    sqlcmd = sqlcmd.Substring(0, 500);
                MessageBox.Show(ex.Message + "\r\n" + sqlcmd, "SqlWrite");
            }
            return ret;
        }
        public int writeUserSql(string sqlcmd)
        {   // SQL write command, returns rows affected
            int ret = 0;
            if (sqlcmd == "")
                return 0;
            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = connstruser;
            SQLiteCommand com = new SQLiteCommand(con);
            try
            {
                con.Open();
                com.CommandText = sqlcmd;
                ret = com.ExecuteNonQuery();
                com.Dispose();
                con.Close();
                con.Dispose();
                writeDebug(sqlcmd + " rows:" + ret);
            }
            catch (SQLiteException ex)
            {
                if (com != null)
                    com.Dispose();
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
                if (sqlcmd.Length > 500)
                    sqlcmd = sqlcmd.Substring(0, 500);
                MessageBox.Show(ex.Message + "\r\n" + sqlcmd, "SqlWrite");
            }
            return ret;
        }
        public string readStatSql(string sqlcmd)
        {   // SQL read first or ; delimited values as string
            string ret;
            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = connstrstat;
            SQLiteCommand com = new SQLiteCommand(con);
            try
            {
                com.CommandText = sqlcmd;
                con.Open();
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    ret = dr.GetValue(0).ToString();
                    for (int i = 1; i < dr.FieldCount; i++)
                        ret += "|" + dr.GetValue(i).ToString();
                }
                else ret = "";
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
                writeDebug(sqlcmd + " ;= " + ret);
                return ret;
            }
            catch (SQLiteException ex)
            {
                if (com != null)
                    com.Dispose();
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
                MessageBox.Show(ex.Message + "\r\n" + sqlcmd, "SqlStatRead");
                return "";
            }
        }
        public string readSql(string sqlcmd)
        {   // SQL read first or ; delimited values as string
            string ret;
            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = connstr;
            SQLiteCommand com = new SQLiteCommand(con);
            try
            {
                com.CommandText = sqlcmd;
                con.Open();
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    ret = dr.GetValue(0).ToString();
                    for (int i = 1; i < dr.FieldCount; i++)
                        ret += "|" + dr.GetValue(i).ToString();
                }
                else ret = "";
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
                writeDebug(sqlcmd + " ;= " + ret);
                return ret;
            }
            catch (SQLiteException ex)
            {
                if (com != null)
                    com.Dispose();
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
                MessageBox.Show(ex.Message + "\r\n" + sqlcmd, "SqlRead");
                return "";
            }
        }
        public string readUserSql(string sqlcmd)
        {   // SQL read first or ; delimited values as string
            string ret;
            SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = connstruser;
            SQLiteCommand com = new SQLiteCommand(con);
            try
            {
                com.CommandText = sqlcmd;
                con.Open();
                SQLiteDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    ret = dr.GetValue(0).ToString();
                    for (int i = 1; i < dr.FieldCount; i++)
                        ret += "|" + dr.GetValue(i).ToString();
                }
                else ret = "";
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
                writeDebug(sqlcmd);
                if (!sqlcmd.Contains(" code "))
                    writeDebug(ret);
                else writeDebug("[encrypted vCode]");
                return ret;
            }
            catch (SQLiteException ex)
            {
                if (com != null)
                    com.Dispose();
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
                MessageBox.Show(ex.Message + "\r\n" + sqlcmd, "SqlRead");
                return "";
            }
        }

        public string readMySql(string sqlcmd)
        {   // SQL read values as string
            string ret;
            MySqlConnection con = new MySqlConnection(mysqlconnstr);
            MySqlCommand com = new MySqlCommand(sqlcmd, con);
            try
            {
                con.Open();
                MySqlDataReader dr = com.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    ret = dr.GetValue(0).ToString();
                    for (int i = 1; i < dr.FieldCount; i++)
                        ret += "|" + dr.GetValue(i).ToString();
                }
                else ret = "";
                dr.Close();
                dr.Dispose();
                com.Dispose();
                con.Close();
                con.Dispose();
                writeDebug(sqlcmd);
                writeDebug(ret);
                return ret;
            }
            catch (MySqlException ex)
            {
                if (com != null)
                    com.Dispose();
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
                MessageBox.Show(ex.Message + "\r\n" + sqlcmd, "MySqlRead");
                return "";
            }
        }
        public int writeMySql(string sqlcmd)
        {   // SQL write command, returns rows affected
            int ret = 0;
            if (sqlcmd == "")
                return 0;
            MySqlConnection con = new MySqlConnection(mysqlconnstr);
            MySqlCommand com = new MySqlCommand(sqlcmd, con);
            try
            {
                con.Open();
                ret = com.ExecuteNonQuery();
                com.Dispose();
                con.Close();
                con.Dispose();
                writeDebug(sqlcmd + " return:"+ret);
            }
            catch (MySqlException ex)
            {
                if (com != null)
                    com.Dispose();
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
                if (sqlcmd.Length > 500)
                    sqlcmd = sqlcmd.Substring(0, 500);
                MessageBox.Show(ex.Message + "\r\n" + sqlcmd, "MySqlWrite");
            }
            return ret;
        }

        public void writeDebug(string strout)
        {
            if (debugging)
            {
                // logfile if enabled
                if (!File.Exists(filepath + "\\debug.txt"))
                {
                    using (StreamWriter outfile = File.CreateText(filepath + "\\debug.txt"))
                    {
                        outfile.WriteLine(strout);
                    }
                }
                else
                {
                    using (StreamWriter outfile = File.AppendText(filepath + "\\debug.txt"))
                    {
                        if (strout.Length > 1000)
                            outfile.WriteLine(strout.Substring(0, 500) + "..." + strout.Substring(strout.Length - 500, 500));
                        else outfile.WriteLine(strout);
                    }
                }
            }
        }
        public void createTables()
        {
            writeSql("DROP TABLE [apiAssets];");
            writeSql("CREATE TABLE [apiAssets] ([itemID] INT64 NOT NULL ON CONFLICT REPLACE, [charID] INT64, [corpID] INT64, [locationID] INT64, [systemID] INT64, [typeID] INT, [quantity] INT,"
                + " [groupID] INT, [categoryID] SMALLINT, [depths] SMALLINT, [parentItemID] INT64, [flag] INT, [singleton] BOOL, [rawQuantity] INT, [trash] BOOL DEFAULT 0,"
                + " CONSTRAINT [sqlite_autoindex_apiAssets_1] PRIMARY KEY ([itemID]) ON CONFLICT REPLACE);");
            writeSql("DROP TABLE [apiItems];");
            writeSql("CREATE TABLE [apiItems] ([itemID] INT64 NOT NULL ON CONFLICT REPLACE, [itemName] VARCHAR(100), [charID] INT64, [corpID] INT64, [groupID] INT, [systemID] INT64, [x] DOUBLE,"
                + " [y] DOUBLE, [z] DOUBLE, [trash] BOOL, CONSTRAINT [sqlite_autoindex_apiItems_1] PRIMARY KEY ([itemID]) ON CONFLICT REPLACE);");
            writeSql("DROP TABLE [apiOutposts];");
            writeSql("CREATE TABLE [apiOutposts] ([stationID] INT NOT NULL ON CONFLICT REPLACE, [stationName] VARCHAR(100), [stationTypeID] INT, [solarSystemID] INT64, [corporationID] INT64,"
                + " [corporationName] VARCHAR(100), [trash] BOOL DEFAULT 0, CONSTRAINT [sqlite_autoindex_apiOutposts_1] PRIMARY KEY ([stationID]) ON CONFLICT REPLACE);");
            writeSql("DROP TABLE [apiSovereignty];");
            writeSql("CREATE TABLE [apiSovereignty] ([solarSystemID] INT NOT NULL ON CONFLICT REPLACE, [allianceID] INT, [factionID] INT, [corporationID] INT64,"
                + "CONSTRAINT [sqlite_autoindex_apiSovereignty_1] PRIMARY KEY ([solarSystemID]) ON CONFLICT REPLACE);");
            writeSql("DROP TABLE [reactions];");
            writeSql("CREATE TABLE [reactions] ([itemID] INT64 NOT NULL, [locationID] INT64 NOT NULL, [corpID] INT64 NOT NULL, [typeID] INT NOT NULL, [groupID] INT NOT NULL, [capacity] INT DEFAULT 0,"
                + " [contensTypeID] INT DEFAULT 0, [quantity] INT DEFAULT 0, [volume] DOUBLE DEFAULT 0, [input] INT DEFAULT 0, [output] INT DEFAULT 0, [direction] VARCHAR(1) DEFAULT (''),"
                + " [remark] VARCHAR(200), [cleared] BOOL DEFAULT 0, [clearedBy] VARCHAR(100) DEFAULT (''), [clearedTimestamp] DATETIME DEFAULT null, [contensName] VARCHAR(100) DEFAULT (''),"
                + " [groupName] VARCHAR(100) DEFAULT (''), CONSTRAINT [sqlite_autoindex_reactions_1] PRIMARY KEY ([itemID]) ON CONFLICT REPLACE);");
            writeSql("DROP TABLE [apiStarbases];");
            writeSql("CREATE TABLE [apiStarbases] ([itemID] INT64 NOT NULL ON CONFLICT REPLACE, [corpID] INT64, [typeID] INT, [locationID] INT64, [moonID] INT64, [state] SMALLINT, [stateTimestamp] TIMESTAMP,"
                + " [onlineTimestamp] TIMESTAMP, [standingOwnerID] INT64, [usageFlags] TINYINT DEFAULT 0, [deployFlags] TINYINT DEFAULT 0, [allowCorporationMembers] BOOL DEFAULT 0, [allowAllianceMembers] BOOL DEFAULT 0,"
                + " [ownerID] INT64 DEFAULT 0, [onStandingDropStanding] FLOAT DEFAULT 0, [onStatusDropEnabled] BOOL DEFAULT 0, [onStatusDropStanding] FLOAT DEFAULT 0, [onAgression] BOOL DEFAULT 0,"
                + " [onCorporationWar] BOOL DEFAULT 0, [fuelBlockTypeID] INT DEFAULT 0, [fuelBlockQuantity] INT DEFAULT 0, [fuelBlockConsumption] INT DEFAULT 40, [strontiumTypeID] INT DEFAULT 16275,"
                + " [strontiumQuantity] INT DEFAULT 0, [strontiumConsumption] INT DEFAULT 400, [charterTypeID] INT DEFAULT 0, [charterQuantity] INT DEFAULT 0, [trash] BOOL DEFAULT 0, [moonName] VARCHAR(100) DEFAULT (''),"
                + " [towerName] VARCHAR(100) DEFAULT (''), [capacityPrimary] FLOAT DEFAULT 0, [capacitySecondary] FLOAT DEFAULT 0, [hasSov] BOOL DEFAULT 1, [groupName] VARCHAR(100) DEFAULT (''),"
                + " [fold] VARCHAR(1) DEFAULT ('6'), [remark] VARCHAR(200), [cleared] BOOL DEFAULT 0, [clearedBy] VARCHAR(100) DEFAULT (''), [clearedTimestamp] TIMESTAMP DEFAULT null,"
                + " CONSTRAINT [sqlite_autoindex_apiStarbases_1] PRIMARY KEY ([itemID]) ON CONFLICT REPLACE);");
        }
    }
}
